Re: [SQL] Contertine timespan to hours - Mailing list pgsql-sql

From Herouth Maoz
Subject Re: [SQL] Contertine timespan to hours
Date
Msg-id l03110703b20ebfeea187@[147.233.159.109]
Whole thread Raw
In response to Contertine timespan to hours  ("Shawn T. Walker" <swalker@iac.net>)
List pgsql-sql
At 21:17 +0300 on 28/8/98, Shawn T. Walker wrote:


>
>     I have a table of a timespan.  I get it in days,hours,and
> minutes.  I need to convert the entire thing to hours.
>
> select sum(totaltime) from usage1998;
>
> returns:
> sum
> -------------------------------------
> @ 12468 days 23 hours 40 mins 45 secs
> (1 row)
>
> I need thevalue in just hours... Can anyone give me a hand...

testing=> \d test3

Table    = test3
+-----------------------------+-----------------------------+-------+
|           Field             |              Type           | Length|
+-----------------------------+-----------------------------+-------+
| ts                          | timespan                    |    12 |
+-----------------------------+-----------------------------+-------+
testing=> SELECT * FROM test3;
ts
------------------------
@ 6 days 4 hours 32 mins
@ 200 days 3 hours
@ 17 hours
@ 3 hours 8 mins ago
(4 rows)

testing=> SELECT % ( date_part( 'epoch', ts ) / 3600 ) FROM test3;
?column?
--------
     148
    4803
      17
      -3
(4 rows)

The date_part with 'epoch' converts the timespan to seconds. You convert it
to hours by dividing by 3600, and you use % to round it.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma



pgsql-sql by date:

Previous
From: "Shawn T. Walker"
Date:
Subject: Contertine timespan to hours
Next
From: Herouth Maoz
Date:
Subject: Re: [SQL] copy one to many?